package com.syni.mdd.yhd.common.all.dao.jpa;

import com.syni.mdd.yhd.common.all.dao.BaseJPADao;
import com.syni.mdd.yhd.common.all.entity.BmsBusiness;
import com.syni.mdd.yhd.common.all.entity.BmsDxOrder;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;

import javax.persistence.EntityListeners;
import java.util.List;
import java.util.Map;
import java.util.Set;

public interface BmsDxOrderDao extends BaseJPADao<BmsDxOrder,Integer> {
    List<BmsDxOrder> findByBmsUserId(Integer userId, Pageable pageable);

    List<BmsDxOrder> findByBmsBusinessId(Integer businessId);

    List<BmsDxOrder> findByIdIn(Set orderIds);

    BmsDxOrder findByIdAndIsDelete(Integer orderId, Integer isDelete);

    @Query(value = "select sum(bdo.total_consum) num ,DATE_FORMAT(new_time,?4) times from bms_dx_order bdo " +
            "where unix_timestamp(bdo.new_time) >= ?1 and unix_timestamp(bdo.new_time) <= ?2 and bdo.bms_business_id = ?3 group by times",nativeQuery = true)
    List<Map<String,Object>> GroupByOrderTime(Integer startTime,Integer endTime,Integer businessId,String pattern);

    @Query(value = "select bdo.id,bdo.total_consum,DATE_FORMAT(bdo.new_time,'%H:%i:%s') new_time,bu.login_name from bms_dx_order bdo LEFT JOIN " +
            "bms_user bu on bdo.bms_user_id = bu.id " +
            "where unix_timestamp(bdo.new_time) >= ?1 and unix_timestamp(bdo.new_time) <= ?2 and bdo.bms_business_id = ?3",nativeQuery = true)
    List<Map<String,Object>> findDayOrder(Integer startTime,Integer endTime,Integer businessId,Pageable pageable);

    @Query(value = "select count(distinct bdo.bms_user_id) from bms_dx_order bdo where bdo.bms_business_id = ?1 and bdo.status = ?2",nativeQuery = true)
    Integer countBusinessConsumerUsersNum(Integer businessId,Integer status);

    @Query(value = "select count(bdo.id) from bms_dx_order bdo left join bms_dx_order_status bdos  " +
            "on bdo.id = bdos.order_id where bdo.bms_user_id = ?1 and " +
            "bdo.bms_coupon_id = ?2 and bdo.order_type = 2 and bdos.status in (1)",nativeQuery = true)
    Integer countUserUseCouponNum(Integer userId,Integer couponId);

    /**通过订单号查找**/
    BmsDxOrder findByOrderNo(String orderNo);

    /**动态sql，查询退款售后订单**/
    @Query(value = "select * from bms_dx_order bdo where bdo.is_delete = 0 and bdo.bms_user_id = ?1 and ( (bdo.order_type in (1,2) and bdo.status in(2)) or " +
            "(bdo.order_type in (3) and bdo.status in(3,4,5)) )",nativeQuery = true)
    List<BmsDxOrder> selfFindOrderRefundAfter(Integer userId, Pageable pageable);

    /**统计买单订单(暂时是买单订单)总成交价**/
    @Query(value = "select IFNULL(sum(bdo.shop_consum),0) num from bms_dx_order_status bdos " +
            "left join bms_dx_order bdo on bdos.order_id = bdo.id where bdo.bms_business_id = ?1 " +
            "and bdo.order_type in (?2) and bdos.status = ?3",nativeQuery = true)
    Double countBusinessOrderTotalByType(Integer businessId,Set orderType,Integer status);

    /**根据时间范围统计买单订单总成交价**/
    @Query(value = "select  IFNULL(sum(bdo.shop_consum),0) num from bms_dx_order_status bs left join bms_dx_order bdo on bs.order_id = bdo.id " +
            " where bdo.bms_business_id in (?1) and bdo.order_type in (?2) and bs.status = ?3 " +
            "and bdo.pay_time >= str_to_date(?4,?6) and bdo.pay_time <= str_to_date(?5,?6)",nativeQuery = true)
    Double countBusinessOrderTotalByTypeAndPayTime(Set businessId,Set orderType,Integer status,
                                         String startTime,String endTime,String dateFormat);
    /**根据时间范围统计买单订单数**/
    @Query(value = "select count(bs.id) from bms_dx_order_status bs left join bms_dx_order bdo on bs.order_id = bdo.id " +
            "where bdo.bms_business_id = ?1 and bdo.order_type in (?2) and " +
            "bs.status = ?3 and bdo.pay_time >= str_to_date(?4,?6) and bdo.pay_time <= str_to_date(?5,?6)",nativeQuery = true)
    Integer countBusinessOrderNum(Integer businessId,Set orderTypes,Integer status,
                                  String startTime,String endTime,String dateFormat);

    /**根据时间范围查找买单订单列表**/
    @Query(value = "select * from bms_dx_order bdo inner join bms_dx_order_status bs on bdo.id = bs.order_id " +
            " where bdo.bms_business_id = ?1 and bdo.order_type in (?2) and " +
            "bs.status = ?3 and bdo.pay_time >= str_to_date(?4,\'%Y%m%d%H%i%s\') " +
            "and bdo.pay_time <= str_to_date(?5,\'%Y%m%d%H%i%s\')",nativeQuery = true)
    List<BmsDxOrder> selfFindOrder(Integer businessId,Set orderTypes,Integer status,String startTime,String endTime,Pageable pageable);

    /**查找买单订单列表**/
    @Query(value = "select * from bms_dx_order bdo inner join bms_dx_order_status bs on bdo.id = bs.order_id " +
            " where bdo.bms_business_id = ?1 and bdo.order_type in (?2) and " +
            "bs.status = ?3",nativeQuery = true)
    List<BmsDxOrder> selfFindOrder(Integer businessId,Set orderTypes,Integer status,Pageable pageable);

    /**查找买单订单列表**/
    @Query(value = "select count(1) from bms_dx_order bdo inner join bms_dx_order_status bs on bdo.id = bs.order_id " +
            " where bdo.bms_business_id = ?1 and bdo.order_type in (?2) and " +
            "bs.status = ?3",nativeQuery = true)
    Integer selfFindOrder(Integer businessId,Set orderTypes,Integer status);

    /**查询用户已下单的团购订单**/
    @Query(value = "select bo.* from bms_dx_order bo left join bms_dx_order_status bdos on bo.id = bdos.order_id " +
            "where bo.bms_user_id = ?1 and bo.bms_group_buy_id =?2 and " +
            "bdos.status = 0 and bo.is_delete = 0",nativeQuery = true)
    List<BmsDxOrder> selfFindCommitGroupBuyOrder(Integer userId, Integer groupBuyId);

    /**查询用户已下单的团购订单**/
    @Query(value = "select sum(bo.platform_buy_num) buyNum from bms_dx_order bo " +
            "left join bms_dx_order_status bdos on bo.id = bdos.order_id " +
            "where bo.bms_user_id = ?1 and " +
            "(bdos.status = 0 or bdos.status = 6) and bo.is_delete = 0",nativeQuery = true)
    Integer countUserBuyMask(Integer userId);
}

